package com.matt.repository;

import com.matt.domain.Leads;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.beans.Transient;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/6/13
 * *******************************
 */
public interface LeadsRepository extends JpaRepository<Leads, String>,JpaSpecificationExecutor<Leads>{

    @Transactional
    @Modifying
    @Query(value = "update leads set status=:status,lastFollowRecord=:lastFollowRecord,lastFollowTime=NOW(),nextTime=:nextTime where id=:leadsId",nativeQuery = true)
    int updateStatus(@Param("leadsId") String leadsId, @Param("status") Integer status, @Param("lastFollowRecord") String lastFollowRecord, Date nextTime);

//    @Modifying
//    @Transactional
//    int deleteById(String leadsId);

    @Transactional
    @Modifying
    @Query(value = "update leads set staffId=:staffId,lastFollowTime=NOW() where id=:leadsId",nativeQuery = true)
    void updateStaff(@Param("staffId") String staffId, @Param("leadsId") String leadsId);


    Leads findByChildNameAndAreaId(String childrenName, String areaId);

//    @Query(value = "select * from leads where childName=:childName and mobile=:mobile and areaId=:areaId and deleted=false",nativeQuery = true)
//    Leads findByChildNameAndMobileAndAreaIdAndDeletedIsFalse(@Param("childName") String childName, @Param("mobile") String mobile, @Param("areaId") String areaId);

    Leads findTopByChildNameAndMobileAndAreaIdAndDeletedFalse(String childrenName, String mobile, String areaId);

    //@Query(value = "SELECT * FROM leads  WHERE areaId = :areaId and id = :leadsId",nativeQuery = true)
    Leads findByIdAndAreaId(String leadsId, String areaId);
    @Query(value = "SELECT * FROM leads " +
            "WHERE id IN (:leadsId) " +  // 匹配列表中的id
            "AND areaId = :areaId",      // 匹配指定的areaId
            nativeQuery = true)
    List<Leads> findByIdAndAreaId1(
            @Param("leadsId") List<String> leadsId,  // 用@Param绑定参数名
            @Param("areaId") String areaId
    );

    //查询指定月份的线索
    @Query(value = "select * from leads where areaId=:areaId and DATE_FORMAT( created, '%Y%m' ) = DATE_FORMAT( :created, '%Y%m' ) ",nativeQuery = true)
    List<Leads> findByAreaIdAndMonth(String areaId,String created);

    @Query(value = "SELECT * FROM leads  WHERE areaId = :areaId and mobile = :mobile",nativeQuery = true)
    Leads findByMobileAreaId(@Param("mobile") String mobile, @Param("areaId") String areaId);

    Leads findByMobile(String mobile);

    @Query(value ="select a.id,a.area_name as name ,a.deleted status,a.latitude,a.longtitude  from  ty2_sso_dev.area a ",nativeQuery = true)
    List<Map<String, Object>> getAllArea();

    // 更简洁的版本（推荐）：DeletedFalse作为条件直接写在方法名中，无需传参
    Leads findTopByChildNameAndMobileAndAreaIdAndNickNameAndBirthdayAndDeletedFalse(
            String childName,
            String mobile,
            String areaId,
            String nickName,
            Date birthday
    );

//    Leads findTopByChildNameAndMobileAndAreaIdAndNickNameAndDeletedFalse(String name, String mobile, String areaId, String nickName);

    @Query("SELECT l FROM Leads l WHERE l.childName = :name AND l.mobile = :mobile AND l.areaId = :areaId " +
            "AND (:nickName IS NULL AND l.nickName IS NULL OR l.nickName = :nickName) " +
            "AND l.deleted = false")
    Leads findTopByChildNameAndMobileAndAreaIdAndNickNameAndDeletedFalse(
            @Param("name") String name,
            @Param("mobile") String mobile,
            @Param("areaId") String areaId,
            @Param("nickName") String nickName
    );
}
